﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-17
修改:
修改时间: 
功能: 企划-周SKC数量确认
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using Nancy.Extensions;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library;
using VerticaAPI;

namespace PlanAPI
{
    [Description("单店系列周SKC数量")]
    public class PLShopSeriesSKCHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        const string TUIPIN = "000005";

        /// Writer：梁家健
        /// Create Date：2017-03-15
        [MustLogin]
        [Description("季-单店-系列SKC数量")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLShopSeriesSKCModel.ListModel>>))]
        public ApiResult GetPLShopSeriesSKCList([ParamDescription("商品区域")] string ParentAreaId="",
            [ParamDescription("季节")] string SeasonName = "",
            [ParamDescription("省份")] string ProID = "", [ParamDescription("城市")] string CityID = "",
            [ParamDescription("店铺")] string ShopIDs = "",
            [ParamDescription("类别")] string KindID = "", [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                if (set == null) return Error("没有要修改企划的月份");
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var count = ReturnPLShopSeriesSKCCount(ParentAreaId, ProID, CityID, shopList, set.Yearv.Value,
                    SeasonName, KindID);
                var result = PaperData<IGrouping<string, PLShopSeriesSKCModel.ListModel>>.Create(count, Size, Page);
                var dt = ReturnPLShopSeriesSKCList(ParentAreaId, ProID, CityID, shopList, set.Yearv.Value,
                    SeasonName, KindID, Page, Size);
                var list = ModelConvertHelper<PLShopSeriesSKCModel.ListModel>.ConvertToModel(dt);
                list.ToList().ForEach(d => d.Monthv = set.Monthv);
                result.Items =
                    list.GroupBy(
                            d => d.Yearv + "_" + d.SeasonID + "_" + d.ParentAreaID + "_" + d.ShopID + "_" + d.KindID)
                        .ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回季-单店-系列SKC数据
        /// </summary>
        /// <param name="ParentAreaId"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Keys"></param>
        /// <param name="Year"></param>
        /// <param name="SeasonID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        /// writer:梁家健
        /// creatdate:2017-03-20
        public DataTable ReturnPLShopSeriesSKCList(string ParentAreaId, string ProID, string CityID, string ShopList, long Year, string SeasonName, string KindID,int Page,int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        skc.SeriesID ,
        skc.MoveAmountRate * 100 AS MoveAmountRate ,
        skc.SupplyAmountRate * 100 AS SupplyAmountRate ,
        skc.SaleAmountRate * 100 AS SaleAmountRate ,
        skc.MSRate * 100 AS MSRate ,
        skc.StandAmountRate * 100 AS StandAmountRate ,
        ISNULL(skc.CkStandAmountRate, skc.StandAmountRate) * 100 AS CkStandAmountRate ,
        skc.GrossRate * 100 AS GrossRate ,
        skc.SKCRate * 100 AS SKCRate ,
        skc.PercentSKCRate * 100 AS PercentSKCRate ,
        skc.DisplayQty ,
        skc.PlanSKC ,
        ISNULL(skc.CheckPlanSKC, skc.PlanSKC) AS CheckPlanSKC ,
        skc.Remark ,
        series.SeriesName
FROM    ( SELECT    *
          FROM      ( SELECT    skc.ExtrSource ,
                                skc.CompanyID ,
                                skc.Yearv ,
                                skc.SeasonID ,
                                skc.Monthv ,
                                skc.ParentAreaID ,
                                skc.ShopID ,
                                skc.KindID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                season.SeasonName ,
                                kind.KindName ,
                                dict.dictName AS ParentAreaName ,
                                ROW_NUMBER() OVER ( ORDER BY skc.Yearv DESC, skc.SeasonID DESC, skc.Monthv DESC, skc.ParentAreaID, skc.ShopID, skc.KindID ) AS Row
                      FROM      ngoods.ngoods_PL_ShopSeriesSKC skc
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON skc.ParentAreaID = dict.dictValue
                                                     AND skc.ExtrSource = dict.ExtrSource
                                                     AND skc.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) shop ON skc.ShopID = shop.ShopID
                                                     AND skc.ExtrSource = shop.ExtrSource
                                                     AND skc.CompanyID = shop.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Season
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) season ON skc.SeasonID = season.SeasonID
                                                       AND skc.ExtrSource = season.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) kind ON skc.KindID = kind.KindID
                                                     AND skc.ExtrSource = kind.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_Bas_Area
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) area ON shop.AreaID = area.AreaID
                                                     AND skc.ExtrSource = area.ExtrSource
                      WHERE     skc.Yearv = {Year}
                                AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND skc.CompanyID = '{UserInfo.UserCompanyId}' 
                                {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and season.SeasonName='{SeasonName}' ")}
                                {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and skc.ParentAreaId='{ParentAreaId}' ")}
                                {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
                                {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                                {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                                {(string.IsNullOrEmpty(KindID) ? "" : $@" and skc.KindID='{KindID}' ")}
                      GROUP BY  skc.ExtrSource ,
                                skc.CompanyID ,
                                skc.Yearv ,
                                skc.SeasonID ,
                                skc.Monthv ,
                                skc.ParentAreaID ,
                                skc.ShopID ,
                                skc.KindID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                season.SeasonName ,
                                kind.KindName ,
                                dict.dictName
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        LEFT JOIN ( SELECT  skc.*
                    FROM    ngoods.ngoods_PL_ShopSeriesSKC skc
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON skc.ShopID = shop.ShopID
                                                 AND skc.ExtrSource = shop.ExtrSource
                                                 AND skc.CompanyID = shop.CompanyID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON shop.AreaID = area.AreaID
                                                 AND skc.ExtrSource = area.ExtrSource
                    WHERE   skc.Yearv = {Year}
                            AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND skc.CompanyID = '{UserInfo.UserCompanyId}' 
                            {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and skc.ParentAreaId='{ParentAreaId}' ")}
                            {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
                            {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                            {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                            {(string.IsNullOrEmpty(KindID) ? "" : $@" and skc.KindID='{KindID}' ")}
                    
                  ) skc ON table2.ExtrSource = skc.ExtrSource
                           AND table2.CompanyID = skc.CompanyID
                           AND table2.Yearv = skc.Yearv
                           AND table2.SeasonID = skc.SeasonID --and table2.Monthv=skc.Monthv 
                           AND table2.ParentAreaID = skc.ParentAreaID
                           AND table2.ShopID = skc.ShopID
                           AND table2.KindID = skc.KindID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Series
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND SeriesID != '{TUIPIN}'
                   ) series ON skc.SeriesID = series.SeriesID
                               AND table2.ExtrSource = series.ExtrSource
ORDER BY table2.Yearv DESC ,
        table2.SeasonID DESC ,
        table2.Monthv DESC ,
        table2.ParentAreaID ,
        table2.ShopID ,
        table2.KindID ,
        skc.SeriesID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回季-单店-系列SKC数量总数
        /// </summary>
        /// <param name="ParentAreaId"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Keys"></param>
        /// <param name="Year"></param> 
        /// <param name="SeasonID"></param>
        /// <param name="SeriesID"></param>
        /// <returns></returns>
        /// writer:梁家健
        /// creatdate:2017-03-20
        public int ReturnPLShopSeriesSKCCount(string ParentAreaId, string ProID, string CityID, string ShopList, long Year, string SeasonName, string KindID)
        {
            try
            {
                var sql = $@"SELECT  COUNT(1)
FROM    ( SELECT    COUNT(1)
          FROM      ngoods.ngoods_PL_ShopSeriesSKC skc
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_global_dictionary
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) dict ON skc.ParentAreaID = dict.dictValue
                                         AND skc.ExtrSource = dict.ExtrSource
                                         AND skc.CompanyID = dict.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_Shop
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) shop ON skc.ShopID = shop.ShopID
                                         AND skc.ExtrSource = shop.ExtrSource
                                         AND skc.CompanyID = shop.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Season
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) season ON skc.SeasonID = season.SeasonID
                                           AND skc.ExtrSource = season.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) kind ON skc.KindID = kind.KindID
                                         AND skc.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Series
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) series ON skc.SeriesID = series.SeriesID
                                           AND skc.ExtrSource = series.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_Bas_Area
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) area ON shop.AreaID = area.AreaID
                                         AND skc.ExtrSource = area.ExtrSource
          WHERE     skc.Yearv = {Year}
                    AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND skc.CompanyID = '{UserInfo.UserCompanyId}'
                    {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and season.SeasonName='{SeasonName}' ")}
                    {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and skc.ParentAreaId = '{ParentAreaId}' ")}
                    {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
                    {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                    {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                    {(string.IsNullOrEmpty(KindID) ? "" : $@" and skc.KindID='{KindID}' ")}
GROUP BY            skc.Yearv ,
                    skc.SeasonID ,
                    skc.ParentAreaID ,
                    skc.ShopID ,
                    skc.KindID
        ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        //        /// Writer：梁家健
        //        /// Create Date：2017-03-20
        //        [JsonType]
        //        [MustLogin]
        //        [Description("修改季-单店-系列SKC数量")]
        //        [ReturnType(typeof(int),"0-成功")]
        //        public ApiResult UpdatePLShopSeriesSKC(PLShopSeriesSKCModel.ModifyModel param)
        //        {
        //            try
        //            {
        //                const string sqlMonth = @"UPDATE goods.goods_PL_ShopSeriesSKC
        //SET CheckPlanSKC={0},CheckState=1,CkStandAmountRate={7},SKCRate={8},PercentSKCRate={9},PlanSKC={10}
        //where Yearv={1} and SeasonID='{2}' and ParentAreaID='{3}' and ShopID='{4}' and KindID='{5}' and SeriesID='{6}' ";
        //                const string sqlWeek = @"UPDATE goods.goods_PL_ShopSeriesSKC_Week
        //SET WeekvPlanSKC={0},CheckWeekvPlanSKC={0},CheckState=1
        //where Yearv={1} and SeasonID='{2}' and Monthv={3} and Weekv={4} and ParentAreaID='{5}' and ShopID='{6}' and KindID='{7}' and SeriesID='{8}' ";
        //                if (param == null) return Error("参数对象为空");
        //                if (param.list != null)
        //                {
        //                    var cmdList = new List<CommandInfo>();
        //                    var planList = new PLBasSalePlanHandler().ReturnAllPLBasSalePlan(param.Year, param.Month);
        //                    if (planList == null || planList.Count <= 0) return Error($@"{param.Year}-{param.Month}里没有上新计划");
        //                    var rateList = new PLBasShopUpdateRateHandler().ReturnAllPLBasShopUpdateRate(param.Year, param.Month);
        //                    if (rateList == null || rateList.Count <= 0) return Error($@"{param.Year}-{param.Month}里没有店铺更新系数");
        //                    var weekList = AllPLShopSeriesSKCWeek(param.Year, param.Month);
        //                    foreach (var item in param.list)
        //                    {
        //                        var cmdMonth = new CommandInfo()
        //                        {
        //                            CommandText =
        //                                string.Format(sqlMonth, item.CheckPlanSKC, param.Year, item.SeasonID,
        //                                    item.ParentAreaID, item.ShopID, item.KindID, item.SeriesID, item.CkStandAmountRate/(decimal)100d,
        //                                    item.SKCRate / (decimal)100d, item.PercentSKCRate / (decimal)100d, item.PlanSKC),
        //                        };
        //                        cmdList.Add(cmdMonth);
        //                        foreach (var week in weekList.ToList().DistinctBy(d=>d.Weekv))
        //                        {
        //                            var plan =
        //                                planList.Where(
        //                                    d =>
        //                                        d.Yearv == param.Year && d.Weekv == week.Weekv &&
        //                                        d.ParentAreaID == item.ParentAreaID).Select(d => d);
        //                            var rate =
        //                                rateList.Where(
        //                                    d =>
        //                                        d.Yearv == param.Year && d.Monthv == param.Month && d.Weekv == week.Weekv &&
        //                                        d.ParentAreaID == item.ParentAreaID && d.ShopID == item.ShopID).Select(d => d);
        //                            var value = item.CheckPlanSKC*(!plan.Any() ? 0 : plan.First().UpdateRate)*
        //                                        (!rate.Any()
        //                                            ? (decimal) 1d
        //                                            : rate.First().ShopRate);
        //                            var cmdWeek = new CommandInfo()
        //                            {
        //                                CommandText =
        //                                    string.Format(sqlWeek, value
        //                                        ,
        //                                        param.Year,
        //                                        item.SeasonID, param.Month, week.Weekv, item.ParentAreaID, item.ShopID,
        //                                        item.KindID,
        //                                        item.SeriesID),
        //                            };
        //                            cmdList.Add(cmdWeek);
        //                        }
        //                    }
        //                    Helper.ExecuteSqlTran(cmdList);
        //                }
        //                return Success(0);
        //            }
        //            catch (Exception ex)
        //            {
        //                return Error(ex.Message);
        //            }
        //        }

        /// Writer：梁家健
        /// Create Date：2017-05-24
        [JsonType]
        [MustLogin]
        [Description("修改季-单店-系列SKC数量")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdatePLShopSeriesSKC(PLShopSeriesSKCModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sqlMonth = $@"UPDATE  ngoods.ngoods_PL_ShopSeriesSKC
SET     CheckPlanSKC = {{0}} ,
        CheckState = 1 ,
        CkStandAmountRate = {{1}} ,
        SKCRate = {{2}} ,
        PercentSKCRate = {{3}} ,
        PlanSKC = {{4}}
WHERE   Yearv = {{5}}
        AND SeasonID = '{{6}}'
        AND ParentAreaID = '{{7}}'
        AND ShopID = '{{8}}'
        AND KindID = '{{9}}'
        AND SeriesID = '{{10}}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var sqlWeek = $@"UPDATE  ngoods.ngoods_PL_ShopSeriesSKC_Week skcWeek
SET     WeekvPlanSKC = table1.CheckPlanSKC*table1.UpdateRate*table1.ShopRate ,
        CheckWeekvPlanSKC = table1.CheckPlanSKC*table1.UpdateRate*table1.ShopRate
FROM(
SELECT  skcWeek.CompanyID ,
        skcWeek.ExtrSource ,
        skcWeek.Yearv ,
        skcWeek.SeasonID ,
        skcWeek.Monthv ,
        skcWeek.Weekv ,
        skcWeek.ParentAreaID ,
        skcWeek.ShopID ,
        skcWeek.KindID ,
        skcWeek.SeriesID ,
        skc.CheckPlanSKC ,
        ISNULL(plan.UpdateRate, 0) AS UpdateRate ,
        ISNULL(rate.ShopRate, 1) AS ShopRate
FROM    ngoods.ngoods_PL_ShopSeriesSKC_Week skcWeek
        INNER JOIN ( SELECT *
                     FROM   ngoods.ngoods_PL_ShopSeriesSKC
                     WHERE  CompanyID = '{UserInfo.UserCompanyId}'
                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                   ) skc ON skcWeek.CompanyID = skc.CompanyID
                            AND skcWeek.ExtrSource = skc.ExtrSource
                            AND skcWeek.Yearv = skc.Yearv
                            AND skcWeek.ParentAreaID = skc.ParentAreaID
                            AND skcWeek.SeasonID = skc.SeasonID
                            AND skcWeek.ShopID = skc.ShopID
                            AND skcWeek.KindID = skc.KindID
                            AND skcWeek.SeriesID = skc.SeriesID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_SalePlan
                    WHERE   CompanyID = '{UserInfo.UserCompanyId}'
                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                  ) plan ON skcWeek.CompanyID = plan.CompanyID
                             AND skcWeek.ExtrSource = plan.ExtrSource
                             AND skcWeek.Yearv = plan.Yearv
                             AND skcWeek.SeasonID = plan.SeasonID
                             AND skcWeek.ParentAreaID = plan.ParentAreaID
                             AND skcWeek.Monthv = plan.Monthv
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_ShopUpdateRate
                    WHERE   CompanyID = '{UserInfo.UserCompanyId}'
                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                  ) rate ON skcWeek.CompanyID = rate.CompanyID
                            AND skcWeek.ExtrSource = rate.ExtrSource
                            AND skcWeek.Yearv = rate.Yearv
                            AND skcWeek.Monthv = rate.Monthv
                            AND skcWeek.Weekv = rate.Weekv
                            AND skcWeek.ParentAreaID = rate.ParentAreaID
                            AND skcWeek.ShopID = rate.ShopID
WHERE   skcWeek.CompanyID = '{UserInfo.UserCompanyId}'
        AND skcWeek.ExtrSource = '{UserInfo.UserExtrSource}'
        AND skcWeek.Yearv = '{param.Year}'
        AND skcWeek.SeasonID = '{param.list.First().SeasonID}'
        AND skcWeek.ParentAreaID = '{param.list.First().ParentAreaID}'
        AND skcWeek.ShopID = '{param.list.First().ShopID}'
        AND skcWeek.KindID = '{param.list.First().KindID}'
GROUP BY skcWeek.CompanyID ,
        skcWeek.ExtrSource ,
        skcWeek.Yearv ,
        skcWeek.SeasonID ,
        skcWeek.Monthv ,
        skcWeek.Weekv ,
        skcWeek.ParentAreaID ,
        skcWeek.ShopID ,
        skcWeek.KindID ,
        skcWeek.SeriesID ,
        skc.CheckPlanSKC ,
        plan.UpdateRate ,
        rate.ShopRate
)table1 WHERE skcWeek.CompanyID = table1.CompanyID
              AND skcWeek.ExtrSource = table1.ExtrSource
              AND skcWeek.Yearv = table1.Yearv
              AND skcWeek.ParentAreaID = table1.ParentAreaID
              AND skcWeek.SeasonID = table1.SeasonID
              AND skcWeek.Weekv = table1.Weekv
              AND skcWeek.ShopID = table1.ShopID
              AND skcWeek.KindID = table1.KindID
              AND skcWeek.SeriesID = table1.SeriesID ";
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmdMonth = new CommandInfo()
                        {
                            CommandText =
                                string.Format(sqlMonth, item.CheckPlanSKC, item.CkStandAmountRate/(decimal) 100d,
                                    item.SKCRate/(decimal) 100d, item.PercentSKCRate/(decimal) 100d, item.PlanSKC,
                                    param.Year, item.SeasonID, item.ParentAreaID, item.ShopID, item.KindID,
                                    item.SeriesID),
                        };
                        cmdList.Add(cmdMonth);
                    }
                    var cmdLast = new CommandInfo
                    {
                        CommandText = sqlWeek,
                    };
                    cmdList.Add(cmdLast);
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 根据年份、月返回周-单店-系列SKC数量
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Month"></param>
        /// <returns></returns>
        public IList<PLShopSeriesSKCWeekModel.NeceModel> AllPLShopSeriesSKCWeek(int Year, int Month)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ngoods.ngoods_PL_ShopSeriesSKC_Week
WHERE   Yearv = {Year}
        AND Monthv = {Month}
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLShopSeriesSKCWeekModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-20
        [MustLogin]
        [Description("周-单店-系列SKC数量")]
        [ReturnType(typeof(PaperData<PLShopSeriesSKCWeekModel.ListModel>))]
        public ApiResult GetPLShopSeriesSKCWeekList([ParamDescription("年")] long Year = -1L,
            [ParamDescription("季节")] string SeasonID = "",
            [ParamDescription("月")] long Month = -1L, [ParamDescription("周")] long? Weekv = null,
            [ParamDescription("商品区域")] string ParentAreaId = "", [ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "", [ParamDescription("店铺")] string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (Year == -1L || string.IsNullOrEmpty(SeasonID) || Month == -1L)
                {
                    var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                    if (set == null) return Error("没有要修改企划的月份");
                    Year = set.Yearv.Value;
                    Month = set.Monthv.Value;
                    SeasonID = set.SeasonID;
                }
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var count = ReturnPLShopSeriesSKCWeekCount(Year, Month, Weekv, ParentAreaId, ProID, CityID, shopList);
                var result = PaperData<PLShopSeriesSKCWeekModel.ListModel>.Create(count, Size, Page);
                var dt = ReturnPLShopSeriesSKCWeekList(Year, Month, Weekv, ParentAreaId, ProID, CityID, shopList, Page, Size);
                var list = ModelConvertHelper<PLShopSeriesSKCWeekModel.ListModel>.ConvertToModel(dt).ToList().GroupBy(
                        d => new {d.Yearv, d.SeasonID, d.Monthv, d.ParentAreaID, d.ShopID, d.KindID})
                    .ToList();
                var query = list.Select(item => new PLShopSeriesSKCWeekModel.ListModel
                {
                    Yearv = item.Key.Yearv,
                    SeasonID = item.Key.SeasonID,
                    SeriesName = item.First().SeasonName,
                    Monthv = item.Key.Monthv,
                    ParentAreaID = item.Key.ParentAreaID,
                    ParentAreaName = item.First().ParentAreaName,
                    ShopID = item.Key.ShopID,
                    ShopName = item.First().ShopName,
                    KindID = item.Key.KindID,
                    KindName = item.First().KindName,
                    list =
                        item.ToList()
                            .GroupBy(
                                d =>
                                    d.Yearv + "_" + d.SeasonID + "_" + d.Monthv + "_" + d.ParentAreaID + "_" +
                                    d.ShopID + "_" + d.KindID + "_" + d.Weekv)
                            .ToList()
                }).ToList();
                result.Items = query;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回周-单店-系列SKC数量
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Weekv"></param>
        /// <param name="ParentAreaId"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-20
        public DataTable ReturnPLShopSeriesSKCWeekList(long Year, long Month, long? Weekv, string ParentAreaID, string ProID, string CityID, string ShopList,int Page,int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        weekSkc.Weekv ,
        weekSkc.SeriesID ,
        weekSkc.Remark ,
        series.SeriesName ,
        weekSkc.WeekvplanSKC ,
        ISNULL(weekSkc.CheckWeekvplanSKC, weekSkc.WeekvplanSKC) AS CheckWeekvplanSKC ,
        skc.CheckplanSKC ,
        plan.UpdateRate ,
        rate.ShopRate
FROM    ( SELECT    *
          FROM      ( SELECT    skc.CompanyID ,
                                skc.ExtrSource ,
                                skc.Yearv ,
                                skc.SeasonID ,
                                skc.Monthv ,
                                skc.ParentAreaID ,
                                skc.ShopID ,
                                skc.KindID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                kind.KindName ,
                                season.SeasonName ,
                                dict.dictName AS ParentAreaName ,
                                ROW_NUMBER() OVER ( ORDER BY skc.Yearv DESC, skc.SeasonID DESC, skc.Monthv DESC, skc.ParentAreaID, skc.ShopID, skc.KindID ) AS Row
                      FROM      ngoods.ngoods_PL_ShopSeriesSKC_Week skc
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON skc.ParentAreaID = dict.dictValue
                                                     AND skc.ExtrSource = dict.ExtrSource
                                                     AND skc.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) shop ON skc.ShopID = shop.ShopID
                                                     AND skc.ExtrSource = shop.ExtrSource
                                                     AND skc.CompanyID = shop.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_Bas_Area
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) area ON shop.AreaID = area.AreaID
                                                     AND skc.ExtrSource = area.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Season
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) season ON skc.SeasonID = season.SeasonID
                                                       AND skc.ExtrSource = season.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) kind ON skc.KindID = kind.KindID
                                                     AND skc.ExtrSource = kind.ExtrSource
                      WHERE     skc.Yearv = {Year}
                                AND skc.Monthv = {Month}
                                AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND skc.CompanyID = '{UserInfo.UserCompanyId}' 
                                {(Weekv.HasValue ? $@"and skc.Weekv='{Weekv}'" : "")}
                                {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and skc.ParentAreaID = '{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
                                {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                                {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                      GROUP BY  skc.CompanyID ,
                                skc.ExtrSource ,
                                skc.Yearv ,
                                skc.SeasonID ,
                                skc.Monthv ,
                                skc.ParentAreaID ,
                                skc.ShopID ,
                                skc.KindID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                kind.KindName ,
                                season.SeasonName ,
                                dict.dictName
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        LEFT JOIN ( SELECT  weekSkc.*
                    FROM    ngoods.ngoods_PL_ShopSeriesSKC_Week weekSkc
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON weekSkc.ShopID = shop.ShopID
                                                 AND weekSkc.ExtrSource = shop.ExtrSource
                                                 AND weekSkc.CompanyID = shop.CompanyID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON shop.AreaID = area.AreaID
                                                 AND weekSkc.ExtrSource = area.ExtrSource
                    WHERE   weekSkc.Yearv = {Year}
                            AND weekSkc.Monthv = {Month}
                            AND weekSkc.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND weekSkc.CompanyID = '{UserInfo.UserCompanyId}' 
							{(Weekv.HasValue ? $@"and weekSkc.Weekv='{Weekv}'" : "")}
							{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and weekSkc.ParentAreaID = '{ParentAreaID}' ")}
							{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
							{(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
							{(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                    
                  ) weekSkc ON table2.CompanyID = weekSkc.CompanyID
                               AND table2.ExtrSource = weekSkc.ExtrSource
                               AND table2.Yearv = weekSkc.Yearv
                               AND table2.SeasonID = weekSkc.SeasonID
                               AND table2.Monthv = weekSkc.Monthv
                               AND table2.ParentAreaID = weekSkc.ParentAreaID
                               AND table2.ShopID = weekSkc.ShopID
                               AND table2.KindID = weekSkc.KindID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Series
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND SeriesID != '{TUIPIN}'
                   ) series ON weekSkc.SeriesID = series.SeriesID
                               AND table2.ExtrSource = series.ExtrSource
        LEFT JOIN ( SELECT  skc.*
                    FROM    ngoods.ngoods_PL_ShopSeriesSKC skc
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON skc.ShopID = shop.ShopID
                                                 AND skc.ExtrSource = shop.ExtrSource
                                                 AND skc.CompanyID = shop.CompanyID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON shop.AreaID = area.AreaID
                                                 AND skc.ExtrSource = area.ExtrSource
                    WHERE   skc.Yearv = {Year}
                            AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND skc.CompanyID = '{UserInfo.UserCompanyId}' 
							{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and skc.ParentAreaID = '{ParentAreaID}' ")}
							{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
							{(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
							{(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                    
                  ) skc ON table2.CompanyID = skc.CompanyID
                           AND table2.ExtrSource = skc.ExtrSource
                           AND table2.Yearv = skc.Yearv
                           AND table2.SeasonID = skc.SeasonID
                           AND table2.ParentAreaID = skc.ParentAreaID
                           AND table2.ShopID = skc.ShopID
                           AND table2.KindID = skc.KindID
                           AND weekSkc.SeriesID = skc.SeriesID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_Saleplan
                    WHERE   Yearv = {Year}
                            AND Monthv = {Month}
                            AND ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}' 
                    {(Weekv.HasValue ? $@"and Weekv='{Weekv}'" : "")}
                    
                  ) plan ON table2.CompanyID = plan.CompanyID
                             AND table2.ExtrSource = plan.ExtrSource
                             AND table2.Yearv = plan.Yearv
                             AND weekSkc.Weekv = plan.Weekv
                             AND table2.ParentAreaID = plan.ParentAreaID
        LEFT JOIN ( SELECT  rate.*
                    FROM    ngoods.ngoods_PL_Bas_ShopUpdateRate rate
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON rate.ShopID = shop.ShopID
                                                 AND rate.ExtrSource = shop.ExtrSource
                                                 AND rate.CompanyID = shop.CompanyID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON shop.AreaID = area.AreaID
                                                 AND rate.ExtrSource = area.ExtrSource
                    WHERE   rate.Yearv = {Year}
                            AND rate.Monthv = {Month}
                            AND rate.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND rate.CompanyID = '{UserInfo.UserCompanyId}' 
							{(Weekv.HasValue ? $@"and Weekv='{Weekv}'" : "")}
							{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and rate.ParentAreaID = '{ParentAreaID}' ")}
							{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
							{(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
							{(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                    
                  ) rate ON table2.CompanyID = rate.CompanyID
                            AND table2.ExtrSource = rate.ExtrSource
                            AND table2.Yearv = rate.Yearv
                            AND table2.Monthv = rate.Monthv
                            AND weekSkc.Weekv = rate.Weekv
                            AND table2.ParentAreaID = rate.ParentAreaID
                            AND table2.ShopID = rate.ShopID
ORDER BY table2.Yearv DESC ,
        table2.SeasonID DESC ,
        table2.Monthv DESC ,
        table2.ParentAreaID ,
        table2.ShopID ,
        table2.KindID ,
        weekSkc.SeriesID ,
        weekSkc.Weekv";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回周-单店-系列SKC数量总条数
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Weekv"></param>
        /// <param name="ParentAreaId"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-20
        public int ReturnPLShopSeriesSKCWeekCount(long Year, long Month, long? Weekv, string ParentAreaID, string ProID, string CityID, string ShopList)
        {
            try
            {
                var sql = $@"SELECT    COUNT(1)
          FROM      ( SELECT    COUNT(1)
                      FROM      ngoods.ngoods_PL_ShopSeriesSKC_Week skc
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON skc.ParentAreaID = dict.dictValue
                                                     AND skc.ExtrSource = dict.ExtrSource
                                                     AND skc.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) shop ON skc.ShopID = shop.ShopID
                                                     AND skc.ExtrSource = shop.ExtrSource
                                                     AND skc.CompanyID = shop.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_Bas_Area
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) area ON shop.AreaID = area.AreaID
                                                     AND skc.ExtrSource = area.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Season
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) season ON skc.SeasonID = season.SeasonID
                                                       AND skc.ExtrSource = season.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) kind ON skc.KindID = kind.KindID
                                                     AND skc.ExtrSource = kind.ExtrSource
                      WHERE     skc.Yearv = {Year}
                                AND skc.Monthv = {Month}
                                AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND skc.CompanyID = '{UserInfo.UserCompanyId}' 
                                {(Weekv.HasValue ? $@"and skc.Weekv='{Weekv}'" : "")}
                                {(string.IsNullOrEmpty(ParentAreaID)
                    ? ""
                    : $@" and skc.ParentAreaID = '{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
                                {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                                {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                      GROUP BY  skc.CompanyID ,
                                skc.ExtrSource ,
                                skc.Yearv ,
                                skc.SeasonID ,
                                skc.Monthv ,
                                skc.ParentAreaID ,
                                skc.ShopID ,
                                skc.KindID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                kind.KindName ,
                                season.SeasonName ,
                                dict.dictName
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-01
        [JsonType]
        [MustLogin]
        [Description("修改周-单店-系列SKC数量")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLShopSeriesSKCWeekModel.ListModel>>))]
        public ApiResult UpdatePLShopSeriesSKCWeek([ParamDescription("周SKC数量")]PLShopSeriesSKCWeekModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"UPDATE  ngoods.ngoods_PL_ShopSeriesSKC_Week
SET     CheckWeekvPlanSKC = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND SeasonID = '{{2}}'
        AND Monthv = {{3}}
        AND Weekv = {{4}}
        AND ParentAreaID = '{{5}}'
        AND ShopID = '{{6}}'
        AND KindID = '{{7}}'
        AND SeriesID = '{{8}}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
		AND ExtrSource = '{UserInfo.UserExtrSource}' ";
                if (param == null) return Error("参数对象为空");
                if (param.list != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.list)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(sql, item.CheckWeekvPlanSKC, item.Yearv, item.SeasonID, item.Monthv,
                                    item.Weekv, item.ParentAreaID, item.ShopID, item.KindID, item.SeriesID)
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-05-26
        public DataTable ExportPLShopSeriesSKC(string Keys)
        {
            try
            {
                var sql = $@"SELECT  skc.CompanyID ,
        skc.ExtrSource ,
        skc.Yearv ,
        season.SeasonName ,
        dict.dictName AS ParentAreaName ,
        shop.ShopCode ,
        shop.ShopName ,
        kind.KindName ,
        series.SeriesName ,
        skc.StandAmountRate * 100 AS StandAmountRate ,
        ISNULL(skc.CkStandAmountRate, skc.StandAmountRate) * 100 AS CkStandAmountRate
FROM    ngoods.ngoods_PL_ShopSeriesSKC skc
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_global_dictionary
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) dict ON skc.ParentAreaID = dict.dictValue
                             AND skc.ExtrSource = dict.ExtrSource
                             AND skc.CompanyID = dict.CompanyID
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) shop ON skc.ShopID = shop.ShopID
                             AND skc.ExtrSource = shop.ExtrSource
                             AND skc.CompanyID = shop.CompanyID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_Bas_Area
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) area ON shop.AreaID = area.AreaID
                             AND skc.ExtrSource = area.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Season
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) season ON skc.SeasonID = season.SeasonID
                               AND skc.ExtrSource = season.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Kind
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) kind ON skc.KindID = kind.KindID
                             AND skc.ExtrSource = kind.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Series
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) series ON skc.SeriesID = series.SeriesID
                               AND skc.ExtrSource = series.ExtrSource
WHERE   skc.ExtrSource = '{UserInfo.UserExtrSource}'
        AND skc.CompanyID = '{UserInfo.UserCompanyId}'
        {(string.IsNullOrEmpty(Keys) ? "" : $"{Keys}")}
ORDER BY skc.Yearv DESC ,
        skc.SeasonID DESC ,
        skc.ParentAreaID ,
        skc.ShopID ,
        skc.KindID ,
        skc.SeriesID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="list"></param>
        /// <param name="index"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-26
        public void CreateTempTable(DataTable dt,string TempTableName, Dictionary<string, string> Cesd)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ExtrSource varchar(30),
    Yearv int,
    SeasonName varchar(120),
    ParentAreaName varchar(80),
    ShopCode varchar(30),
    ShopName varchar(120),
    KindName varchar(120),
    SeriesName varchar(180),
    CkStandAmountRate numeric(8,4)
)";
                if (dt.Columns.Contains("StandAmountRate"))
                {
                    dt.Columns.Remove("StandAmountRate");
                }
                if (!dt.Columns.Contains("ExtrSource"))
                {
                    dt.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                    foreach (DataRow item in dt.Rows)
                    {
                        var companyID = item["CompanyID"].ToString();
                        item["ExtrSource"] = Cesd.Keys.Contains(companyID) ? Cesd[companyID] : "";
                    }
                }
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-06-10
        public DataTable ImportShopSeriesSKC(string TempTableName, Dictionary<string, string> Cesd)
        {
            var uid = Guid.NewGuid().ToString().Replace("-", "");
            var finalTempTableName = $" ngoods.ngoods_{uid} ";
            try
            {
                var sql = $@" SELECT  table1.CompanyID ,
        table1.Yearv ,
        table1.SeasonName ,
        table1.ParentAreaName ,
        table1.ShopCode ,
        table1.ShopName ,
        table1.KindName ,
        table1.SeriesName ,
        table1.CkStandAmountRate ,
        shop.ShopID ,
        dict.dictValue AS ParentAreaID ,
        season.SeasonID ,
        kind.KindID ,
        series.SeriesID ,
        skc.StandAmountRate
FROM    {TempTableName} table1
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON table1.ShopCode = shop.ShopCode
                            AND table1.ExtrSource = shop.ExtrSource
                            AND table1.CompanyID = shop.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_global_dictionary
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) dict ON table1.ParentAreaName = dict.dictName
                            AND table1.ExtrSource = dict.ExtrSource
                            AND table1.CompanyID = dict.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Season
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) season ON table1.SeasonName = season.SeasonName
                              AND table1.ExtrSource = season.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Kind
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) kind ON table1.KindName = kind.KindName
                            AND table1.ExtrSource = kind.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Series
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) series ON table1.SeriesName = series.SeriesName
                              AND table1.ExtrSource = series.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_ShopSeriesSKC
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) skc ON skc.CompanyID = table1.CompanyID
                           AND skc.ExtrSource = table1.ExtrSource
                           AND skc.Yearv = table1.Yearv
                           AND skc.SeasonID = season.SeasonID
                           AND skc.ParentAreaID = dict.dictValue
                           AND skc.ShopID = shop.ShopID
                           AND skc.KindID = kind.KindID
                           AND skc.SeriesID = series.SeriesID ";
                var companyID = UserInfo.UserCompanyId;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLShopSeriesSKCModel.ListModel>.ConvertToModel(dt);
                var newList =
                    list.Where(
                        d => d.CompanyID == companyID && Cesd.Keys.Contains(d.CompanyID) &&
                             d.ShopID != null && d.ParentAreaID != null && d.SeasonID != null && d.KindID != null &&
                             d.SeriesID != null && d.StandAmountRate != null).Select(d => d).ToList();
                newList.ForEach(d => { d.ExtrSource = Cesd[d.CompanyID]; });
                var resultList =
                    newList.GroupBy(d => new {d.CompanyID, d.Yearv, d.SeasonID, d.ParentAreaID, d.ShopID, d.KindID})
                        .Where(d => d.Sum(r => r.CkStandAmountRate) >= 0 && d.Sum(r => r.CkStandAmountRate) <= 100)
                        .SelectMany(d => d)
                        .DistinctBy(
                            d => new {d.CompanyID, d.Yearv, d.SeasonID, d.ParentAreaID, d.ShopID, d.KindID, d.SeriesID})
                        .ToList();
                var resultDT = DataTableHelper.LinqToDataTable(resultList);
                if (resultDT.Rows.Count > 0)
                {
                    resultDT.Columns.Remove("ShopCode");
                    resultDT.Columns.Remove("ShopName");
                    resultDT.Columns.Remove("KindName");
                    resultDT.Columns.Remove("SeriesName");
                    resultDT.Columns.Remove("SeasonName");
                    resultDT.Columns.Remove("ParentAreaName");
                }
                DropTempTable(finalTempTableName);
                Helper.CreateTempTable(finalTempTableName, "ngoods.ngoods_PL_ShopSeriesSKC");
                UpdateShopSeriesSKCByTempTable(finalTempTableName);
                var errorList = list.Except(resultList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                //if (errorDT.Rows.Count > 0)
                //{
                //    errorDT.Columns.Remove("Monthv");
                //    errorDT.Columns.Remove("ShopID");
                //    errorDT.Columns.Remove("ParentAreaID");
                //    errorDT.Columns.Remove("SeasonID");
                //    errorDT.Columns.Remove("KindID");
                //    errorDT.Columns.Remove("SeriesID");
                //    errorDT.Columns.Remove("MoveAmountRate");
                //    errorDT.Columns.Remove("SupplyAmountRate");
                //    errorDT.Columns.Remove("SaleAmountRate");
                //    errorDT.Columns.Remove("MSRate");
                //    errorDT.Columns.Remove("StandAmountRate");
                //    errorDT.Columns.Remove("GrossRate");
                //    errorDT.Columns.Remove("SKCRate");
                //    errorDT.Columns.Remove("PercentSKCRate");
                //    errorDT.Columns.Remove("DisplayQty");
                //    errorDT.Columns.Remove("PlanSKC");
                //    errorDT.Columns.Remove("CheckPlanSKC");
                //    errorDT.Columns.Remove("Remark");
                //}
                return errorDT;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DropTempTable(finalTempTableName);
            }
        }

        /// <summary>
        /// 根据临时表更新
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-26
        public void UpdateShopSeriesSKCByTempTable(string TempTableName)
        {
            try
            {
                var sqlUpdateSKCRate = $@"update
	ngoods.ngoods_PL_ShopSeriesSKC skc
set
	CkStandAmountRate = table2.CkStandAmountRate / 100,
	SKCRate =(
		table2.CkStandAmountRate / 100
	) * skc.GrossRate
from
	(
		SELECT  table1.CompanyID ,
        table1.ExtrSource ,
        table1.Yearv ,
        MAX(table1.CkStandAmountRate) AS CkStandAmountRate ,
        table1.ShopID ,
        table1.ParentAreaID ,
        table1.SeasonID ,
        table1.KindID ,
        table1.SeriesID
FROM    {TempTableName} table1
WHERE   table1.CompanyID = '{UserInfo.UserCompanyId}'
        AND table1.ExtrSource = '{UserInfo.UserExtrSource}'
GROUP BY table1.CompanyID ,
        table1.ExtrSource ,
        table1.Yearv ,
        table1.ShopID ,
        table1.ParentAreaID ,
        table1.SeasonID ,
        table1.KindID ,
        table1.SeriesID
	) table2
where skc.CompanyID = '{UserInfo.UserCompanyId}'
    AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
	AND skc.CompanyID = table2.CompanyID
	AND skc.ExtrSource = table2.ExtrSource
	AND skc.Yearv = table2.Yearv
	AND skc.SeasonID = table2.SeasonID
	AND skc.ParentAreaID = table2.ParentAreaID
	AND skc.ShopID = table2.ShopID
	AND skc.KindID = table2.KindID
	AND skc.SeriesID = table2.SeriesID";
                var sqlUpdatePercentSKCRate = $@"update  ngoods.ngoods_PL_ShopSeriesSKC skc
set PercentSKCRate = CASE WHEN table2.SKCRate = 0 THEN 0
             ELSE skc.SKCRate / table2.SKCRate
        END
        from ( SELECT  skc.CompanyID ,
        skc.ExtrSource ,
        skc.Yearv ,
        skc.SeasonID ,
        skc.ParentAreaID ,
        skc.ShopID ,
        skc.KindID ,
        SUM(skc.SKCRate) AS SKCRate
FROM    ngoods.ngoods_PL_ShopSeriesSKC skc
        INNER JOIN ( SELECT *
                     FROM   {TempTableName}
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) table1 ON skc.CompanyID = table1.CompanyID
                                             AND skc.ExtrSource = table1.ExtrSource
                                             AND skc.Yearv = table1.Yearv
                                             AND skc.ShopID = table1.ShopID
                                             AND skc.ParentAreaID = table1.ParentAreaID
                                             AND skc.SeasonID = table1.SeasonID
                                             AND skc.KindID = table1.KindID
                                             AND skc.SeriesID = table1.SeriesID
WHERE   skc.CompanyID = '{UserInfo.UserCompanyId}'
        AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
GROUP BY skc.CompanyID ,
        skc.ExtrSource ,
        skc.Yearv ,
        skc.SeasonID ,
        skc.ParentAreaID ,
        skc.ShopID ,
        skc.KindID
             ) table2 where skc.CompanyID = '{UserInfo.UserCompanyId}'
                    AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND skc.CompanyID = table2.CompanyID
             		AND skc.ExtrSource = table2.ExtrSource
                    AND skc.Yearv = table2.Yearv
                    AND skc.SeasonID = table2.SeasonID
                    AND skc.ParentAreaID = table2.ParentAreaID
                    AND skc.ShopID = table2.ShopID
                    AND skc.KindID = table2.KindID";
                var sqlUpdatePlanSKC = $@"update  ngoods.ngoods_PL_ShopSeriesSKC skc
set PlanSKC = skc.DisplayQty * skc.PercentSKCRate ,
        CheckPlanSKC = skc.DisplayQty * skc.PercentSKCRate
        from ( SELECT  skc.CompanyID ,
        skc.ExtrSource ,
        skc.Yearv ,
        skc.SeasonID ,
        skc.ParentAreaID ,
        skc.ShopID ,
        skc.KindID ,
        skc.SeriesID
FROM    ngoods.ngoods_PL_ShopSeriesSKC skc
        INNER JOIN ( SELECT *
                     FROM   {TempTableName}
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) table1 ON skc.CompanyID = table1.CompanyID
                             AND skc.ExtrSource = table1.ExtrSource
                             AND skc.Yearv = table1.Yearv
                             AND skc.ShopID = table1.ShopID
                             AND skc.ParentAreaID = table1.ParentAreaID
                             AND skc.SeasonID = table1.SeasonID
                             AND skc.KindID = table1.KindID
                             AND skc.SeriesID = table1.SeriesID
WHERE   skc.CompanyID = '{UserInfo.UserCompanyId}'
        AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
GROUP BY skc.CompanyID ,
        skc.ExtrSource ,
        skc.Yearv ,
        skc.SeasonID ,
        skc.ParentAreaID ,
        skc.ShopID ,
        skc.KindID ,
        skc.SeriesID
             ) table2 where skc.CompanyID = '{UserInfo.UserCompanyId}'
                         AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                         AND skc.CompanyID = table2.CompanyID
             			 AND skc.ExtrSource = table2.ExtrSource
                         AND skc.Yearv = table2.Yearv
                         AND skc.SeasonID = table2.SeasonID
                         AND skc.ParentAreaID = table2.ParentAreaID
                         AND skc.ShopID = table2.ShopID
                         AND skc.KindID = table2.KindID
                         AND skc.SeriesID = table2.SeriesID";
                var sqlUpdateWeekvPlanSKC = $@"update  ngoods.ngoods_PL_ShopSeriesSKC_Week skcWeek
set WeekvPlanSKC = table1.CheckPlanSKC * table1.UpdateRate
        * table1.ShopRate ,
        CheckWeekvPlanSKC = table1.CheckPlanSKC * table1.UpdateRate
        * table1.ShopRate
FROM    ( SELECT  skcWeek.CompanyID ,
        skcWeek.ExtrSource ,
        skcWeek.Yearv ,
        skcWeek.SeasonID ,
        skcWeek.Monthv ,
        skcWeek.Weekv ,
        skcWeek.ParentAreaID ,
        skcWeek.ShopID ,
        skcWeek.KindID ,
        skcWeek.SeriesID ,
        skc.CheckplanSKC ,
        ISNULL(plan.UpdateRate, 0) AS UpdateRate ,
        ISNULL(rate.ShopRate, 1) AS ShopRate
FROM    ngoods.ngoods_PL_ShopSeriesSKC_Week skcWeek
        INNER JOIN ( SELECT skc.CompanyID ,
                            skc.ExtrSource ,
                            skc.Yearv ,
                            skc.SeasonID ,
                            skc.ParentAreaID ,
                            skc.ShopID ,
                            skc.KindID ,
                            skc.SeriesID ,
                            MAX(skc.CheckplanSKC) AS CheckplanSKC
                     FROM   ngoods.ngoods_PL_ShopSeriesSKC skc
                            INNER JOIN ( SELECT *
                                         FROM   {TempTableName}
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) table1 ON skc.CompanyID = table1.CompanyID
                                                   AND skc.ExtrSource = table1.ExtrSource
                                                   AND skc.Yearv = table1.Yearv
                                                   AND skc.ShopID = table1.ShopID
                                                   AND skc.ParentAreaID = table1.ParentAreaID
                                                   AND skc.SeasonID = table1.SeasonID
                                                   AND skc.KindID = table1.KindID
                                                   AND skc.SeriesID = table1.SeriesID
                     WHERE  skc.CompanyID = '{UserInfo.UserCompanyId}'
                            AND skc.ExtrSource = '{UserInfo.UserExtrSource}'
                     GROUP BY skc.CompanyID ,
                            skc.ExtrSource ,
                            skc.Yearv ,
                            skc.SeasonID ,
                            skc.ParentAreaID ,
                            skc.ShopID ,
                            skc.KindID ,
                            skc.SeriesID
                   ) skc ON skcWeek.CompanyID = skc.CompanyID
                            AND skcWeek.ExtrSource = skc.ExtrSource
                            AND skcWeek.Yearv = skc.Yearv
                            AND skcWeek.ParentAreaID = skc.ParentAreaID
                            AND skcWeek.SeasonID = skc.SeasonID
                            AND skcWeek.ShopID = skc.ShopID
                            AND skcWeek.KindID = skc.KindID
                            AND skcWeek.SeriesID = skc.SeriesID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_Saleplan
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) plan ON skcWeek.CompanyID = plan.CompanyID
                             AND skcWeek.ExtrSource = plan.ExtrSource
                             AND skcWeek.Yearv = plan.Yearv
                             AND skcWeek.SeasonID = plan.SeasonID
                             AND skcWeek.ParentAreaID = plan.ParentAreaID
                             AND skcWeek.Monthv = plan.Monthv
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_PL_Bas_ShopUpdateRate
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) rate ON skcWeek.CompanyID = rate.CompanyID
                            AND skcWeek.ExtrSource = rate.ExtrSource
                            AND skcWeek.Yearv = rate.Yearv
                            AND skcWeek.Monthv = rate.Monthv
                            AND skcWeek.Weekv = rate.Weekv
                            AND skcWeek.ParentAreaID = rate.ParentAreaID
                            AND skcWeek.ShopID = rate.ShopID
WHERE   skcWeek.CompanyID = '{UserInfo.UserCompanyId}'
        AND skcWeek.ExtrSource = '{UserInfo.UserExtrSource}'
GROUP BY skcWeek.CompanyID ,
        skcWeek.ExtrSource ,
        skcWeek.Yearv ,
        skcWeek.SeasonID ,
        skcWeek.Monthv ,
        skcWeek.Weekv ,
        skcWeek.ParentAreaID ,
        skcWeek.ShopID ,
        skcWeek.KindID ,
        skcWeek.SeriesID ,
        skc.CheckplanSKC ,
        plan.UpdateRate ,
        rate.ShopRate
        ) table1 where skcWeek.CompanyID = '{UserInfo.UserCompanyId}'
                    AND skcWeek.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND skcWeek.CompanyID = table1.CompanyID
        			AND skcWeek.ExtrSource = table1.ExtrSource
                    AND skcWeek.Yearv = table1.Yearv
                    AND skcWeek.ParentAreaID = table1.ParentAreaID
                    AND skcWeek.SeasonID = table1.SeasonID
                    AND skcWeek.Weekv = table1.Weekv
                    AND skcWeek.ShopID = table1.ShopID
                    AND skcWeek.KindID = table1.KindID
                    AND skcWeek.SeriesID = table1.SeriesID";
                var cmdListLast = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sqlUpdateSKCRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdatePercentSKCRate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdatePlanSKC,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdateWeekvPlanSKC,
                    },
                };
                Helper.ExecuteSqlTran(cmdListLast);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-26
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
